package com.lei.sqlGoodsJsp.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.lei.sqlGoodsJsp.dao.LoginDao;
import com.lei.sqlGoodsJsp.entity.Goods;
import com.lei.sqlGoodsJsp.entity.LoginUserEntity;

public class LoginDaoImpl implements LoginDao  {

	@Override
	public void add(LoginUserEntity id) {
		Connection conn = JDBC.getConn();
		String sql = "insert into table_login(login_user,login_ip,login_status)values(?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, id.getLoginUser());
			ps.setString(2, id.getLoginIp());
			ps.setString(3, id.getLoginStatus());
			
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		
	}

	@Override
	public List<LoginUserEntity> listAll() {
		Connection conn = JDBC.getConn();
		String sql = "Select id,login_user,login_ip,login_status,login_time table_login from table_login";
		PreparedStatement ps = null;
		List<LoginUserEntity> list = new ArrayList();
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id =rs.getLong(i++);
				String loginUser=rs.getString(i++);
				String loginIp=rs.getString(i++);
				String loginStatus=rs.getString(i++);
				Date loginTime=rs.getDate(i++);
				
				list.add(new LoginUserEntity(id,loginUser,loginIp,loginStatus,loginTime));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		JDBC.close(null, ps, conn);
		}
		return null;
	}

	@Override
	public LoginUserEntity getById(Long id) {
		Connection conn = JDBC.getConn();
		String sql = "select id,login_user,login_ip,login_status,login_time from table_login where id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				int i = 1;
				LoginUserEntity goods = new LoginUserEntity();
				goods.setId(rs.getLong(i++));
				goods.setLoginUser(rs.getString(i++));
				goods.setLoginIp(rs.getString(i++));
				goods.setLoginStatus(rs.getString(i++));
				goods.setLoginTime(rs.getDate(i++));
				return goods;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(null, ps, conn);
		return null;
	}

	@Override
	public int getCount() {
		Connection conn = JDBC.getConn();
		String sql = "select count(1) from table_login ";
		Statement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.createStatement();
			rs = ps.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return 0;
	}

	@Override
	public List<LoginUserEntity> getPage(int currentPage, int pageSize) {
		Connection conn = JDBC.getConn();
		String sql = "select id,login_user,login_ip,login_status,login_time from table_login limit ?,?";
		PreparedStatement ps = null; 
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			//1  0,5
			//2  5,5
			//3  10,5
			ps.setLong(1, (currentPage-1)*pageSize);
			ps.setLong(2, pageSize);
			rs = ps.executeQuery();
			List<LoginUserEntity> list = new ArrayList<>();
			while (rs.next()) {
				int i = 1;
				LoginUserEntity goods = new LoginUserEntity();
				goods.setId(rs.getLong(i++));
				goods.setLoginUser(rs.getString(i++));;
				goods.setLoginIp(rs.getString(i++));
				goods.setLoginStatus(rs.getString(i++));
				goods.setLoginTime(rs.getDate(i++));
				list.add(goods);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		JDBC.close(rs, ps, conn);
		return null;
	}
	
}
